/*
 * @(#)UserDao.java
 * 2011-5-9 下午09:07:30
 *
 * Copyright (c) 2018-2028, HangZhou QiYun InfoTech Co.,Ltd. .
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.qyxx.platform.common.orm.hibernate;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.EntityMode;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.Subqueries;
import org.hibernate.internal.CriteriaImpl;
import org.hibernate.metadata.ClassMetadata;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.Assert;

import com.google.common.collect.Lists;
import com.qyxx.platform.common.orm.Page;
import com.qyxx.platform.common.orm.PropertyFilter;
import com.qyxx.platform.common.orm.PropertyFilter.MatchType;
import com.qyxx.platform.common.utils.encode.EncodeUtils;
import com.qyxx.platform.common.utils.reflection.ReflectionUtils;
import com.qyxx.platform.sysmng.accountmng.entity.User;


/**
 *  动态模型Hibernate Dao
 *  
 *  @author gxj
 *  @version 1.0
 *  @since 1.6 2011-5-9 下午09:07:30
 */
@SuppressWarnings("unchecked")
public class DynamicHibernateDao<T, PK extends Serializable> {
	protected Logger logger = LoggerFactory.getLogger(getClass());
	
	//查找类型：第一条
	public static final String FIND_TYPE_FIRST = "first";
	//查找类型：最后一条
	public static final String FIND_TYPE_LAST = "last";
	//查找类型：上一条
	public static final String FIND_TYPE_PRE = "pre";
	//查找类型：下一条
	public static final String FIND_TYPE_NEXT = "next";

	protected SessionFactory sessionFactory;
	
	//实体名称
	protected String entityName;
	
	/**
	 * 
	 */
	public DynamicHibernateDao() {
		// TODO Auto-generated constructor stub
	}

	/**
	 * 
	 * @param entityName
	 */
	public DynamicHibernateDao(String entityName) {
		this.entityName = entityName;
	}

	/**
	 * 
	 * @param sessionFactory
	 * @param entityName
	 */
	public DynamicHibernateDao(final SessionFactory sessionFactory, final String entityName) {
		this.sessionFactory = sessionFactory;
		this.entityName = entityName;
	}

	/**
	 * 取得sessionFactory.
	 */
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	/**
	 * 采用@Autowired按类型注入SessionFactory, 当有多个SesionFactory的时候在子类重载本函数.
	 */
	@Autowired
	public void setSessionFactory(final SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	
	/**
	 * @return entityName
	 */
	public String getEntityName() {
		return entityName;
	}

	
	/**
	 * @param entityName
	 */
	public void setEntityName(String entityName) {
		this.entityName = entityName;
	}

	/**
	 * 取得当前Session.
	 */
	public Session getSession() {
		return sessionFactory.getCurrentSession();//.getSession(EntityMode.MAP);
	}

	/**
	 * 保存新增或修改的对象.
	 */
	public void save(final T entity) {
		Assert.notNull(entity, "entity不能为空");
		getSession().saveOrUpdate(entityName, entity);
		logger.debug("save entity: {}", entity);
	}
	
	/**
	 * 保存新增或修改的对象.
	 * 
	 * @param entity 实体对象
	 * @param etName 实体名
	 */
	public void save(final T entity, String etName) {
		Assert.notNull(entity, "entity不能为空");
		getSession().saveOrUpdate(etName, entity);
		logger.debug("save entity: {}", entity);
	}

	/**
	 * 删除对象.
	 * 
	 * @param entity 对象必须是session中的对象或含id属性的transient对象.
	 */
	public void delete(final T entity) {
		Assert.notNull(entity, "entity不能为空");
		getSession().delete(entityName, entity);
		logger.debug("delete entity: {}", entity);
	}

	/**
	 * 按id删除对象.
	 */
	public void delete(final PK id) {
		Assert.notNull(id, "id不能为空");
		delete(get(id));
		logger.debug("delete entity {},id is {}", entityName, id);
	}
	
	/**
	 * 删除对象.
	 * 
	 * @param entity 对象必须是session中的对象或含id属性的transient对象.
	 * @param etName 实体名
	 */
	public void delete(final T entity, String etName) {
		Assert.notNull(entity, "entity不能为空");
		getSession().delete(etName, entity);
		logger.debug("delete entity: {}", entity);
	}

	/**
	 * 按id删除对象.
	 * 
	 * @param id
	 * @param etName
	 */
	public void delete(final PK id, String etName) {
		Assert.notNull(id, "id不能为空");
		delete(get(id, etName), etName);
		logger.debug("delete entity {},id is {}", etName, id);
	}

	/**
	 * 按id获取对象.
	 * 
	 * @param id
	 */
	public T get(final PK id) {
		Assert.notNull(id, "id不能为空");
		return (T) getSession().load(entityName, id);
	}
	
	/**
	 * 按id获取对象.
	 * 
	 * @param id
	 * @param etName
	 */
	public T get(final PK id, String etName) {
		Assert.notNull(id, "id不能为空");
		return (T) getSession().load(etName, id);
	}

	/**
	 * 按id列表获取对象列表.
	 */
	public List<T> get(final Collection<PK> ids) {
		return find(Restrictions.in(getIdName(), ids));
	}

	/**
	 *	获取全部对象.
	 */
	public List<T> getAll() {
		return find();
	}

	/**
	 *	获取全部对象, 支持按属性行序.
	 */
	public List<T> getAll(String orderByProperty, boolean isAsc) {
		Criteria c = createCriteria();
		if (isAsc) {
			c.addOrder(Order.asc(orderByProperty));
		} else {
			c.addOrder(Order.desc(orderByProperty));
		}
		return c.list();
	}

	/**
	 * 按属性查找对象列表, 匹配方式为相等.
	 */
	public List<T> findBy(final String propertyName, final Object value) {
		Assert.hasText(propertyName, "propertyName不能为空");
		Criterion criterion = Restrictions.eq(propertyName, value);
		return find(criterion);
	}

	/**
	 * 按属性查找唯一对象, 匹配方式为相等.
	 */
	public T findUniqueBy(final String propertyName, final Object value) {
		Assert.hasText(propertyName, "propertyName不能为空");
		Criterion criterion = Restrictions.eq(propertyName, value);
		return (T) createCriteria(criterion).uniqueResult();
	}

	/**
	 * 按HQL查询对象列表.
	 * 
	 * @param values 数量可变的参数,按顺序绑定.
	 */
	public <X> List<X> find(final String hql, final Object... values) {
		return createQuery(hql, values).list();
	}

	/**
	 * 按HQL查询对象列表.
	 * 
	 * @param values 命名参数,按名称绑定.
	 */
	public <X> List<X> find(final String hql, final Map<String, ?> values) {
		return createQuery(hql, values).list();
	}

	/**
	 * 按HQL查询唯一对象.
	 * 
	 * @param values 数量可变的参数,按顺序绑定.
	 */
	public <X> X findUnique(final String hql, final Object... values) {
		return (X) createQuery(hql, values).uniqueResult();
	}

	/**
	 * 按HQL查询唯一对象.
	 * 
	 * @param values 命名参数,按名称绑定.
	 */
	public <X> X findUnique(final String hql, final Map<String, ?> values) {
		return (X) createQuery(hql, values).uniqueResult();
	}

	/**
	 * 执行HQL进行批量修改/删除操作.
	 * 
	 * @param values 数量可变的参数,按顺序绑定.
	 * @return 更新记录数.
	 */
	public int batchExecute(final String hql, final Object... values) {
		return createQuery(hql, values).executeUpdate();
	}

	/**
	 * 执行HQL进行批量修改/删除操作.
	 * 
	 * @param values 命名参数,按名称绑定.
	 * @return 更新记录数.
	 */
	public int batchExecute(final String hql, final Map<String, ?> values) {
		return createQuery(hql, values).executeUpdate();
	}

	/**
	 * 根据查询HQL与参数列表创建Query对象.
	 * 与find()函数可进行更加灵活的操作.
	 * 
	 * @param values 数量可变的参数,按顺序绑定.
	 */
	public Query createQuery(final String queryString, final Object... values) {
		Assert.hasText(queryString, "queryString不能为空");
		Query query = getSession().createQuery(queryString);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				query.setParameter(i, values[i]);
			}
		}
		return query;
	}

	/**
	 * 根据查询HQL与参数列表创建Query对象.
	 * 与find()函数可进行更加灵活的操作.
	 * 
	 * @param values 命名参数,按名称绑定.
	 */
	public Query createQuery(final String queryString, final Map<String, ?> values) {
		Assert.hasText(queryString, "queryString不能为空");
		Query query = getSession().createQuery(queryString);
		if (values != null) {
			query.setProperties(values);
		}
		return query;
	}

	/**
	 * 按Criteria查询对象列表.
	 * 
	 * @param criterions 数量可变的Criterion.
	 */
	public List<T> find(final Criterion... criterions) {
		return createCriteria(criterions).list();
	}

	/**
	 * 按Criteria查询唯一对象.
	 * 
	 * @param criterions 数量可变的Criterion.
	 */
	public T findUnique(final Criterion... criterions) {
		return (T) createCriteria(criterions).uniqueResult();
	}

	/**
	 * 根据Criterion条件创建Criteria.
	 * 与find()函数可进行更加灵活的操作.
	 * 
	 * @param criterions 数量可变的Criterion.
	 */
	public Criteria createCriteria(final Criterion... criterions) {
		Criteria criteria = getSession().createCriteria(entityName, "aa");
		for (Criterion c : criterions) {
			criteria.add(c);
		}
		return criteria;
	}

	/**
	 * 初始化对象.
	 * 使用load()方法得到的仅是对象Proxy, 在传到View层前需要进行初始化.
	 * 如果传入entity, 则只初始化entity的直接属性,但不会初始化延迟加载的关联集合和属性.
	 * 如需初始化关联属性,需执行:
	 * Hibernate.initialize(user.getRoles())，初始化User的直接属性和关联集合.
	 * Hibernate.initialize(user.getDescription())，初始化User的直接属性和延迟加载的Description属性.
	 */
	public void initProxyObject(Object proxy) {
		Hibernate.initialize(proxy);
	}

	/**
	 * Flush当前Session.
	 */
	public void flush() {
		Session s = getSession();
		s.flush();
		s.clear();
	}

	/**
	 * 为Query添加distinct transformer.
	 * 预加载关联对象的HQL会引起主对象重复, 需要进行distinct处理.
	 */
	public Query distinct(Query query) {
		query.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
		return query;
	}

	/**
	 * 为Criteria添加distinct transformer.
	 * 预加载关联对象的HQL会引起主对象重复, 需要进行distinct处理.
	 */
	public Criteria distinct(Criteria criteria) {
		criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
		return criteria;
	}

	/**
	 * 取得对象的主键名.
	 */
	public String getIdName() {
		ClassMetadata meta = getSessionFactory().getClassMetadata(entityName);
		return meta.getIdentifierPropertyName();
	}

	/**
	 * 判断对象的属性值在数据库内是否唯一.
	 * 
	 * 在修改对象的情景下,如果属性新修改的值(value)等于属性原来的值(orgValue)则不作比较.
	 */
	public boolean isPropertyUnique(final String propertyName, final Object newValue, final Object oldValue) {
		if (newValue == null || newValue.equals(oldValue)) {
			return true;
		}
		Object object = findUniqueBy(propertyName, newValue);
		return (object == null);
	}
	
	/**
	 * 按Criteria分页查询.
	 * 
	 * @param page 分页参数.
	 * @param criterions 数量可变的Criterion.
	 * 
	 * @return 分页查询结果.附带结果列表及所有查询输入参数.
	 */
	public Page<T> findPage(final Page<T> page, final Criterion... criterions) {
		Assert.notNull(page, "page不能为空");

		Criteria c = createCriteria(criterions);

		if (page.isAutoCount()) {
			long totalCount = countCriteriaResult(c);
			page.setTotalCount(totalCount);
		}

		setPageParameterToCriteria(c, page);

		List result = c.list();
		page.setResult(result);
		return page;
	}
	
	/**
	 * 设置分页参数到Criteria对象,辅助函数.
	 */
	protected Criteria setPageParameterToCriteria(final Criteria c, final Page<T> page) {

		Assert.isTrue(page.getPageSize() > 0, "Page Size must larger than zero");

		//hibernate的firstResult的序号从0开始
		c.setFirstResult(page.getFirst() - 1);
		c.setMaxResults(page.getPageSize());

		if (page.isOrderBySetted()) {
			String[] orderByArray = StringUtils.split(page.getOrderBy(), ',');
			String[] orderArray = StringUtils.split(page.getOrder(), ',');

			Assert.isTrue(orderByArray.length == orderArray.length, "分页多重排序参数中,排序字段与排序方向的个数不相等");

			for (int i = 0; i < orderByArray.length; i++) {
				if (Page.ASC.equals(orderArray[i])) {
					c.addOrder(Order.asc(orderByArray[i]));
				} else {
					c.addOrder(Order.desc(orderByArray[i]));
				}
			}
		}
		return c;
	}
	
	/**
	 * 设置排序条件到Criteria对象,辅助函数.
	 * 
	 * @param c
	 * @param page
	 * @return
	 */
	protected Criteria setOrderByParameterToCriteria(final Criteria c, final Page<T> page) {
		if (page.isOrderBySetted()) {
			String[] orderByArray = StringUtils.split(page.getOrderBy(), ',');
			String[] orderArray = StringUtils.split(page.getOrder(), ',');
			Assert.isTrue(orderByArray.length == orderArray.length, "分页多重排序参数中,排序字段与排序方向的个数不相等");
			for (int i = 0; i < orderByArray.length; i++) {
				if (Page.ASC.equals(orderArray[i])) {
					c.addOrder(Order.asc(orderByArray[i]));
				} else {
					c.addOrder(Order.desc(orderByArray[i]));
				}
			}
		}
		return c;
	}
	
	/**
	 * 执行count查询获得本次Criteria查询所能获得的对象总数.
	 */
	protected long countCriteriaResult(final Criteria c) {
		CriteriaImpl impl = (CriteriaImpl) c;

		// 先把Projection、ResultTransformer、OrderBy取出来,清空三者后再执行Count操作
		Projection projection = impl.getProjection();
		ResultTransformer transformer = impl.getResultTransformer();

		List<CriteriaImpl.OrderEntry> orderEntries = null;
		try {
			orderEntries = (List) ReflectionUtils.getFieldValue(impl, "orderEntries");
			ReflectionUtils.setFieldValue(impl, "orderEntries", new ArrayList());
		} catch (Exception e) {
			logger.error("不可能抛出的异常:{}", e.getMessage());
		}

		// 执行Count查询
		Long totalCountObject = (Long) c.setProjection(Projections.rowCount()).uniqueResult();
		long totalCount = (totalCountObject != null) ? totalCountObject : 0;

		// 将之前的Projection,ResultTransformer和OrderBy条件重新设回去
		c.setProjection(projection);

		if (projection == null) {
			c.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
		}
		if (transformer != null) {
			c.setResultTransformer(transformer);
		}
		try {
			ReflectionUtils.setFieldValue(impl, "orderEntries", orderEntries);
		} catch (Exception e) {
			logger.error("不可能抛出的异常:{}", e.getMessage());
		}

		return totalCount;
	}
	
	/**
	 * 按属性查找对象列表,支持多种匹配方式.
	 * 
	 * @param matchType 匹配方式,目前支持的取值见PropertyFilter的MatcheType enum.
	 */
	public List<T> findBy(final String propertyName, final Object value, final MatchType matchType) {
		Criterion criterion = buildCriterion(propertyName, value, matchType);
		return find(criterion);
	}

	/**
	 * 按属性过滤条件列表查找对象列表.
	 */
	public List<T> find(List<PropertyFilter> filters) {
		Criterion[] criterions = buildCriterionByPropertyFilter(filters);
		return find(criterions);
	}
	
	/**
	 * 条件查询列表，加排序
	 * 
	 * @param filters
	 * @param page
	 * @return
	 */
	public List<T> find(List<PropertyFilter> filters, final Page<T> page) {
		Criterion[] criterions = buildCriterionByPropertyFilter(filters);
		Criteria c = createCriteria(criterions);
		setOrderByParameterToCriteria(c, page);
		return c.list();
	}

	/**
	 * 按属性过滤条件列表分页查找对象.
	 */
	public Page<T> findPage(final Page<T> page, final List<PropertyFilter> filters) {
		Criterion[] criterions = buildCriterionByPropertyFilter(filters);
		return findPage(page, criterions);
	}

	/**
	 * 按属性条件参数创建Criterion,辅助函数.
	 */
	protected Criterion buildCriterion(final String propertyName, final Object propertyValue, final MatchType matchType) {
		Assert.hasText(propertyName, "propertyName不能为空");
		Criterion criterion = null;
		//根据MatchType构造criterion
		switch (matchType) {
		case EQ:
			criterion = Restrictions.eq(propertyName, propertyValue);
			break;
		case NE:
			criterion = Restrictions.ne(propertyName, propertyValue);
		case LIKE:
			String[] pvs = StringUtils.split((String) propertyValue);
			Conjunction cjt = Restrictions.conjunction();
			for(String pv : pvs) {
				cjt.add(Restrictions.like(propertyName, pv, MatchMode.ANYWHERE));
			}
			//criterion = Restrictions.like(propertyName, (String) propertyValue, MatchMode.ANYWHERE);
			criterion = cjt;
			break;
		case LIKEE:
			criterion = Restrictions.like(propertyName, (String) propertyValue, MatchMode.EXACT);
			break;	
		case LIKEL:
			criterion = Restrictions.like(propertyName, (String) propertyValue, MatchMode.START);
			break;	
		case LIKER:
			criterion = Restrictions.like(propertyName, (String) propertyValue, MatchMode.END);
			break;	
		case LE:
			criterion = Restrictions.le(propertyName, propertyValue);
			break;
		case LT:
			criterion = Restrictions.lt(propertyName, propertyValue);
			break;
		case GE:
			criterion = Restrictions.ge(propertyName, propertyValue);
			break;
		case GT:
			criterion = Restrictions.gt(propertyName, propertyValue);
			break;
		case USER:
			Disjunction disjunction = Restrictions.disjunction();
			disjunction.add(Restrictions.like("loginName", (String) propertyValue, MatchMode.ANYWHERE));
			disjunction.add(Restrictions.like("realName", (String) propertyValue, MatchMode.ANYWHERE));
			disjunction.add(Restrictions.like("nickname", (String) propertyValue, MatchMode.ANYWHERE));
			DetachedCriteria userIdField = DetachedCriteria.forClass(User.class)
						.setProjection(Property.forName("id")).add(disjunction);
			criterion = Property.forName(propertyName).in(userIdField);
			break;
		}
		return criterion;
	}
	
	/**
	 * 按属性条件参数创建Criterion,辅助函数.
	 */
	protected Criterion buildCriterion(final String propertyName, final Object propertyValue, final MatchType matchType, final Collection values) {
		Assert.hasText(propertyName, "propertyName不能为空");
		Criterion criterion = null;
		if(StringUtils.contains(propertyName, "|||")) {
			String[] ss = StringUtils.split(propertyName, "|||");
			String[] ee = StringUtils.split(entityName,".");
			if(ss.length >= 2 && ee.length >= 2) {
				String subEntityName = ss[0];
				String pn = ss[1];
				DetachedCriteria subQuery = DetachedCriteria.forEntityName(subEntityName, "mm");
				Criterion subCr = buildCriterion(pn, propertyValue, matchType, values);
				subQuery.add(subCr);
				String reEn = EncodeUtils.toLowerOrUpperCaseFirstChar(ee[1], true);//主实体首字母转小写
				subQuery.add(Property.forName("mm."+reEn+".id").eqProperty("aa.id"));
				subQuery.setProjection(Projections.property("mm.id"));
				criterion = Subqueries.exists(subQuery);
			}
		} else {
			//根据MatchType构造criterion
			switch (matchType) {
			case EQ:
				criterion = Restrictions.eq(propertyName, propertyValue);
				break;
			case NE:
				criterion = Restrictions.ne(propertyName, propertyValue);	
				break;
			case LIKE:
				String[] pvs = StringUtils.split((String) propertyValue);
				Conjunction cjt = Restrictions.conjunction();
				for(String pv : pvs) {
					cjt.add(Restrictions.like(propertyName, pv, MatchMode.ANYWHERE));
				}
				//criterion = Restrictions.like(propertyName, (String) propertyValue, MatchMode.ANYWHERE);
				criterion = cjt;
				break;
			case LIKEE:
				criterion = Restrictions.like(propertyName, (String) propertyValue, MatchMode.EXACT);
				break;	
			case LIKEL:
				criterion = Restrictions.like(propertyName, (String) propertyValue, MatchMode.START);
				break;	
			case LIKER:
				criterion = Restrictions.like(propertyName, (String) propertyValue, MatchMode.END);
				break;	
			case LE:
				criterion = Restrictions.le(propertyName, propertyValue);
				break;
			case LT:
				criterion = Restrictions.lt(propertyName, propertyValue);
				break;
			case GE:
				criterion = Restrictions.ge(propertyName, propertyValue);
				break;
			case GT:
				criterion = Restrictions.gt(propertyName, propertyValue);
				break;
			case IN:
				criterion = Restrictions.in(propertyName, values);
				break;
			case USER:
				Disjunction disjunction = Restrictions.disjunction();
				disjunction.add(Restrictions.like("loginName", (String) propertyValue, MatchMode.ANYWHERE));
				disjunction.add(Restrictions.like("realName", (String) propertyValue, MatchMode.ANYWHERE));
				disjunction.add(Restrictions.like("nickname", (String) propertyValue, MatchMode.ANYWHERE));
				DetachedCriteria userIdField = DetachedCriteria.forClass(User.class)
							.setProjection(Property.forName("id")).add(disjunction);
				criterion = Property.forName(propertyName).in(userIdField);	
				break;
			}
		}
		return criterion;
	}

	/**
	 * 按属性条件列表创建Criterion数组,辅助函数.
	 */
	protected Criterion[] buildCriterionByPropertyFilter(final List<PropertyFilter> filters) {
		List<Criterion> criterionList = new ArrayList<Criterion>();
		for (PropertyFilter filter : filters) {
			if (!filter.hasMultiProperties()) { //只有一个属性需要比较的情况.
				if(filter.hasOrProperties()) { //or子条件
					Disjunction disjunction = Restrictions.disjunction();
					Criterion criterion = buildCriterion(filter.getPropertyName(), filter.getMatchValue(), filter
							.getMatchType(), filter.getValues());
					disjunction.add(criterion);
					for (PropertyFilter orPf: filter.getOrPropertyFilters()) {
						if(!orPf.hasMultiProperties()) {//只有一个属性情况
							Criterion orct = buildCriterion(orPf.getPropertyName(), orPf.getMatchValue(), orPf.getMatchType(), orPf.getValues());
							disjunction.add(orct);
						} else {
							for(String param : orPf.getPropertyNames()) {
								Criterion orct = buildCriterion(param, orPf.getMatchValue(), orPf.getMatchType(), orPf.getValues());
								disjunction.add(orct);
							}
						}
					}
					criterionList.add(disjunction);
				} else { //非or条件
					Criterion criterion = buildCriterion(filter.getPropertyName(), filter.getMatchValue(), filter
							.getMatchType(), filter.getValues());
					criterionList.add(criterion);
				}
			} else {//包含多个属性需要比较的情况,进行or处理.
				Disjunction disjunction = Restrictions.disjunction();
				for (String param : filter.getPropertyNames()) {
					Criterion criterion = buildCriterion(param, filter.getMatchValue(), filter.getMatchType(), filter.getValues());
					disjunction.add(criterion);
				}
				criterionList.add(disjunction);
			}
		}
		return criterionList.toArray(new Criterion[criterionList.size()]);
	}

	/**
	 * 根据sql语句及查询条件查询列表
	 * 
	 * @param sql
	 * @param filters
	 * @return
	 */
	public List<Map<String, Object>> findBy(String sql, final List<PropertyFilter> filters) {
		Query query = getSqlQuery(sql, filters);
		return query.list();
	}
	
	public Query getSqlQuery(String sql, final List<PropertyFilter> filters) {
		Query query = getSession().createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		if(null!=filters && !filters.isEmpty()) {
			for(PropertyFilter pf : filters) {
				if(pf.getIsInMatchType()) {
					query.setParameterList(pf.getPropertyName(), pf.getValues());
				} else {
					query.setParameter(pf.getPropertyName(), pf.getMatchValue());
				}
			}
		}
		return query;
	}
	
	/**
	 * 指定数量查询
	 * 
	 * @param sql
	 * @param filters
	 * @param pageSize
	 * @return
	 */
	public List<Map<String, Object>> findBy(String sql, final List<PropertyFilter> filters, int pageSize) {
		Query query = getSqlQuery(sql, filters);
		query.setFirstResult(0);
		query.setMaxResults(pageSize);
		return query.list();
	}
	
	/**
	 * 根据sql语句及分页参数、查询条件查询分页列表
	 * 
	 * @param sql
	 * @param filters
	 * @param page
	 * @return
	 */
	public Page<T> findBy(String sql, final List<PropertyFilter> filters, Page<T> page) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		preHandleFixFilter(filters, paramMap);
		return this.pagedQuery(sql, page, paramMap);
	}
	
	/**
	 * 执行sql语句
	 * 
	 * @param sql
	 * @param filters
	 * @return
	 */
	public int execSql(String sql, List<PropertyFilter> filters) {
		Query query = getSession().createSQLQuery(sql);
		if(null!=filters && !filters.isEmpty()) {
			for(PropertyFilter pf : filters) {
				if(pf.getIsInMatchType()) {
					query.setParameterList(pf.getPropertyName(), pf.getValues());
				} else {
					query.setParameter(pf.getPropertyName(), pf.getMatchValue());
				}
			}
		}
		return query.executeUpdate();
	}
	
	/**
	 * 根据hql语句及查询条件查询列表
	 * 
	 * @param sql
	 * @param filters
	 * @return
	 */
	public List<Map<String, Object>> findByHql(String sql, final List<PropertyFilter> filters) {
		Query query = getQuery(sql, filters);
		return query.list();
	}
	
	/**
	 * 获取hql查询
	 * 
	 * @param hql
	 * @param filters
	 * @param isResultToMap
	 * @return
	 */
	public Query getQuery(String hql, final List<PropertyFilter> filters, boolean isResultToMap) {
		Query query = getSession().createQuery(hql);
		if(isResultToMap) query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		if(null!=filters && !filters.isEmpty()) {
			for(PropertyFilter pf : filters) {
				if(pf.getIsInMatchType()) {
					query.setParameterList(pf.getPropertyName(), pf.getValues());
				} else {
					query.setParameter(pf.getPropertyName(), pf.getMatchValue());
				}
			}
		}
		return query;
	}
	
	/**
	 * 获取默认结果为Map类型的hql查询
	 * 
	 * @param hql
	 * @param filters
	 * @return
	 */
	public Query getQuery(String hql, final List<PropertyFilter> filters) {
		return getQuery(hql, filters, true);
	}
	
	/**
	 * 指定数量HQL查询
	 * 
	 * @param sql
	 * @param filters
	 * @param pageSize
	 * @return
	 */
	public List<Map<String, Object>> findByHql(String sql, final List<PropertyFilter> filters, int pageSize) {
		Query query = getQuery(sql, filters);
		query.setFirstResult(0);
		query.setMaxResults(pageSize);
		return query.list();
	}
	
	/**
	 * 分页的HQL查询
	 * 
	 * @param hql
	 * @param filters
	 * @param page
	 * @return
	 */
	public List<Map<String, Object>> findByHql(String hql, final List<PropertyFilter> filters, Page<Map<String, Object>> page) {
		Query query = getQuery(hql, filters);
		query.setFirstResult((page.getPageNo()-1)*page.getPageSize());
		query.setMaxResults(page.getPageSize());
		return query.list();
	}
	
	/**
	 * 分页的HQL查询
	 * 
	 * @param hql
	 * @param filters
	 * @param page
	 * @return
	 */
	public Page<Map<String, Object>> findPageByHql(String hql, final List<PropertyFilter> filters, Page<Map<String, Object>> page) {
		if(page.isAutoCount()){
			page.setTotalCount(getTotalCountByHql(hql, filters));
		}
		Query query = getQuery(hql, filters);
		query.setFirstResult((page.getPageNo()-1)*page.getPageSize());
		query.setMaxResults(page.getPageSize());
		page.setResult(query.list());
		return page;
	}
	
	/**
	 * 统计数量，根据hql语句
	 * 
	 * @param hql
	 * @param filters
	 * @return
	 */
	public Long getTotalCountByHql(String hql, final List<PropertyFilter> filters) {
		String countQueryString = " select count(*) " + removeSelect(removeOrders(hql));
		Number totalCount = (Number)getQuery(countQueryString, filters, false).uniqueResult();
		return totalCount.longValue();
	}
	
	/**
	 * 根据排序及查询条件，查找指定ID记录的上一条、下一条、第一条、最后一条记录
	 * 目前只支持1个排序条件
	 * @param page 排序条件
	 * @param filters 查询条件
	 * @param pkName 主键名称
	 * @param id 主键
	 * @param findType 记录类型，详见FIND_TYPE_FIRST等
	 * @return
	 */
	public T findBy(final Page<T> page, final List<PropertyFilter> filters, String pkName, final PK id, String findType) {
		Criterion[] criterions = buildCriterionByPropertyFilter(filters);
		Criteria c = createCriteria(criterions);
		c.setFirstResult(0);
		c.setMaxResults(1);
		String[] orderByArray = StringUtils.split(page.getOrderBy(), ',');
		String[] orderArray = StringUtils.split(page.getOrder(), ',');
		String orderBy = "id";
		if(null!=orderByArray) {
			orderBy = orderByArray[0];
		}
		String order = "desc";
		if(null!=orderArray) {
			order = orderArray[0];
		}
/*		for (int i = 0; i < orderByArray.length; i++) {
			if (Page.ASC.equals(orderArray[i])) {
				c.addOrder(Order.asc(orderByArray[i]));
			} else {
				c.addOrder(Order.desc(orderByArray[i]));
			}
		}*/
		if(FIND_TYPE_FIRST.equals(findType)) { //第一条
			if (Page.ASC.equals(order)) {
				c.addOrder(Order.asc(orderBy));
			} else {
				c.addOrder(Order.desc(orderBy));
			}
		} else if(FIND_TYPE_LAST.equals(findType)) { //最后一条
			if (Page.ASC.equals(order)) {
				c.addOrder(Order.desc(orderBy));
			} else {
				c.addOrder(Order.asc(orderBy));
			}
		} else if(FIND_TYPE_PRE.equals(findType)) { //上一条
			DetachedCriteria orderByField = DetachedCriteria.forEntityName(entityName)
				.setProjection(Property.forName(orderBy)).add(Restrictions.eq(pkName, id));
			//不等于当前值
			c.add(Restrictions.ne(pkName, id));
			if (Page.ASC.equals(order)) {
				c.add(Property.forName(orderBy).le(orderByField)); // <=
				c.addOrder(Order.desc(orderBy));
			} else {
				c.add(Property.forName(orderBy).ge(orderByField)); // >=
				c.addOrder(Order.asc(orderBy));
			}
		} else { //默认下一条
			DetachedCriteria orderByField = DetachedCriteria.forEntityName(entityName)
				.setProjection(Property.forName(orderBy)).add(Restrictions.eq(pkName, id));
			//不等于当前值
			c.add(Restrictions.ne(pkName, id));
			if (Page.ASC.equals(order)) {
				c.add(Property.forName(orderBy).ge(orderByField)); // >=
				c.addOrder(Order.asc(orderBy));
			} else {
				c.add(Property.forName(orderBy).le(orderByField)); // <=
				c.addOrder(Order.desc(orderBy));
			}
		}
		/*if (page.isOrderBySetted()) {
			Assert.isTrue(orderByArray.length == orderArray.length, "分页多重排序参数中,排序字段与排序方向的个数不相等");
			for (int i = 0; i < orderByArray.length; i++) {
				if (Page.ASC.equals(orderArray[i])) {
					c.addOrder(Order.asc(orderByArray[i]));
				} else {
					c.addOrder(Order.desc(orderByArray[i]));
				}
			}
		}*/
		return (T)c.uniqueResult();
	}
	
	/**
	 * 根据sql进行分页查询
	 * 
	 * @param sql
	 * @param page
	 * @param values
	 * @return
	 */
	public Page<T> pagedQuery(String sql, final Page<T> page, Object... values) {
		// Count查询
		if(page.isAutoCount()){
			page.setTotalCount(getTotalCountBySql(sql, values));
		}
		// 实际查询返回分页对象
		Query query = createSQLQuery(sql, values);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List result = query.setFirstResult(page.getFirst()-1).setMaxResults(page.getPageSize()).list();
		page.setResult(result);
		return page;
	}
	
	/**
	 * 根据sql进行分页查询
	 * 
	 * @param sql
	 * @param page
	 * @param paramMap
	 * @return
	 */
	public Page<T> pagedQuery(String sql, final Page<T> page, Map<String,Object> paramMap) {
		// Count查询
		if(page.isAutoCount()){
			page.setTotalCount(getTotalCountBySql(sql, paramMap));
		}
		// 实际查询返回分页对象
		Query query = createSQLQuery(sql, paramMap);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List result = query.setFirstResult(page.getFirst()-1).setMaxResults(page.getPageSize()).list();
		page.setResult(result);
		return page;
	}
	
	/**
	 * 统计数量，根据sql语句
	 * 
	 * @param sql
	 * @param values
	 * @return
	 */
	public Long getTotalCountBySql(String sql, Object... values) {
		String countQueryString = " select count(*) " + removeSelect(removeOrders(sql));
		Number totalCount = (Number)createSQLQuery(countQueryString, values).uniqueResult();
		return totalCount.longValue();
	}
	
	/**
	 * 统计数量，根据sql语句
	 * 
	 * @param sql
	 * @param paramMap
	 * @return
	 */
	public Long getTotalCountBySql(String sql, Map<String,Object> paramMap) {
		String countQueryString = " select count(*) " + removeSelect(removeOrders(sql));
		//String countQueryString = " select count(*) " + removeSelect(hql);
		Number totalCount = (Number)createSQLQuery(countQueryString, paramMap).uniqueResult();
		return totalCount.longValue();
	}
	
	/**
	 * 统计数量，根据hql语句
	 * 
	 * @param hql
	 * @param values
	 * @return
	 */
	public Long getTotalCountByHql(String hql, Object... values) {
		String countQueryString = " select count(*) " + removeSelect(removeOrders(hql));
		Number totalCount = (Number)createQuery(countQueryString, values).uniqueResult();
		return totalCount.longValue();
	}
	
	
	/**
	 * 根据SQL语句分页查询
	 * 
	 * @param sql
	 * @param page
	 * @param filters
	 * @return
	 */
	public Page<T> findPage(String sqlSelect, final Page<T> page, final List<PropertyFilter> filters) {
		List<Object> paramList = new ArrayList<Object>();
		String sql = buildHqlByPropertyFilter(sqlSelect, filters, paramList);
		sql = buildOrderByHql(sql, page);
		return pagedQuery(sql, page, paramList.toArray());
	}
	
	/**
	 * 根据SQL语句分页查询
	 * 固定参数+动态参数
	 * 
	 * @param sqlSelect
	 * @param page
	 * @param filters
	 * @param fixFilters
	 * @return
	 */
	public Page<T> findPage(String sqlSelect, final Page<T> page, final List<PropertyFilter> filters, final List<PropertyFilter> fixFilters) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		preHandleFixFilter(fixFilters, paramMap);
		String sql = buildHqlByPropertyFilter(sqlSelect, filters, paramMap);
		sql = buildOrderByHql(sql, page);
		return pagedQuery(sql, page, paramMap);
	}

	/**
	 * 预设置固定参数
	 * 
	 * @param fixFilters
	 * @param paramMap
	 */
	public void preHandleFixFilter(final List<PropertyFilter> fixFilters, Map<String, Object> paramMap) {
		for (PropertyFilter filter : fixFilters) {
			if (!filter.hasMultiProperties()) { //只有一个属性需要比较的情况.
				String param = filter.getPropertyName();
				switch(filter.getMatchType()) {
					case IN:
						paramMap.put(param, filter.getValues());
						break;
					case LIKE:
						paramMap.put(param, "%"+filter.getMatchValue().toString().trim()+"%");
						break;
					case LIKEE:
						paramMap.put(param, filter.getMatchValue().toString().trim());
						break;	
					case LIKEL:
						paramMap.put(param, filter.getMatchValue().toString().trim()+"%");
						break;	
					case LIKER:
						paramMap.put(param, "%"+filter.getMatchValue().toString().trim());
						break;	
					default:
						paramMap.put(param, filter.getMatchValue());
				}
			} else {//包含多个属性需要比较的情况,进行or处理.
				for (String param : filter.getPropertyNames()) {
					switch(filter.getMatchType()) {
						case IN:
							paramMap.put(param, filter.getValues());
							break;
						case LIKE:
							paramMap.put(param, "%"+filter.getMatchValue().toString().trim()+"%");
							break;	
						case LIKEE:
							paramMap.put(param, filter.getMatchValue().toString().trim());
							break;	
						case LIKEL:
							paramMap.put(param, filter.getMatchValue().toString().trim()+"%");
							break;	
						case LIKER:
							paramMap.put(param, "%"+filter.getMatchValue().toString().trim());
							break;	
						default:
							paramMap.put(param, filter.getMatchValue());
					}
				}
			}
		}
	}

	/**
	 * 根据SQL语句查询列表，不加排序条件
	 * 
	 * @param hqlSelect
	 * @param filters
	 * @return
	 */
	public List<T> find(final String hqlSelect, final List<PropertyFilter> filters) {
		List<Object> paramList = new ArrayList<Object>();
		String hql = buildHqlByPropertyFilter(hqlSelect, filters, paramList);
		Query query = createSQLQuery(hql, paramList.toArray());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
	
	/**
	 * 根据SQL语句查询列表，加排序条件
	 * 
	 * @param hqlSelect
	 * @param filters
	 * @param page
	 * @return
	 */
	public List<T> find(final String hqlSelect, final List<PropertyFilter> filters, final Page<T> page) {
		List<Object> paramList = new ArrayList<Object>();
		String hql = buildHqlByPropertyFilter(hqlSelect, filters, paramList);
		hql = buildOrderByHql(hql, page);
		Query query = createSQLQuery(hql, paramList.toArray());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
	
	/**
	 * 按HQL查询唯一对象.
	 * 
	 * @param values 数量可变的参数,按顺序绑定.
	 */
	public Map<String, Object> findUniqueByHql(final String hql, final Object... values) {
		Query query = createQuery(hql, values);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> list = query.list();
		if(null!=list && !list.isEmpty()) {
			return list.get(0);
		} else {
			return new HashMap<String, Object>();
		}
	}

	/**
	 * 去除hql的orderby 子句，用于pagedQuery.
	 * 
	 * @param hql
	 * @return
	 */
	private static String removeOrders(String hql) {
		Assert.hasText(hql);
		/*Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(hql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		return sb.toString();*/
		Pattern p = Pattern.compile("order\\s*by\\s*[\\w\\s,.]*[\\s]*", Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(hql);
		int start = 0;
		int len = hql.length();
		while (m.find()) {
			if(m.end()==len) { //获取最后一个order by子句的开始位置
				start = m.start();
			}
		}
		if(start > 0) {
			hql = hql.substring(0, start);
		}
		return hql;
	}

	/**
	 * 去除hql的select子句，未考虑union的情况,用于pagedQuery.
	 * 
	 * @param hql
	 * @return
	 */
	private static String removeSelect(String hql) {
		Assert.hasText(hql);
		int beginPos = hql.toLowerCase().indexOf("from");
		Assert.isTrue(beginPos != -1, " hql : " + hql + " must has a keyword 'from'");
		return hql.substring(beginPos);
	}
	
	/**
	 * 根据PropertyFilter动态生成hql语句
	 * 
	 * @param hqlSelect
	 * @param filters
	 * @param paramList
	 * @return
	 */
	protected String buildHqlByPropertyFilter(final String hqlSelect, final List<PropertyFilter> filters, final List<Object> paramList) {
		StringBuffer sb = new StringBuffer();
		sb.append(hqlSelect);
		int beginPos = hqlSelect.toLowerCase().indexOf("where");
		if(beginPos == -1) {
			sb.append(" where 1=1");
		}
		for (PropertyFilter filter : filters) {
			if (!filter.hasMultiProperties()) { //只有一个属性需要比较的情况.
				sb.append(" and ");
				if(filter.hasOrProperties()) { //or条件
					List<String> tmpList = new ArrayList<String>();
					String cs = buildHql(filter.getPropertyName(), filter.getMatchValue(), filter
							.getMatchType(), filter.getValues(), paramList);
					tmpList.add(cs);
					for(PropertyFilter orPf : filter.getOrPropertyFilters()) {
						if(!orPf.hasMultiProperties()) {//单个属性
							String conSql = buildHql(orPf.getPropertyName(), orPf.getMatchValue(), orPf
									.getMatchType(), orPf.getValues(), paramList);
							tmpList.add(conSql);
						} else {
							for(String param : filter.getPropertyNames()) {
								String conSql = buildHql(param, orPf.getMatchValue(), orPf
										.getMatchType(), orPf.getValues(), paramList);
								tmpList.add(conSql);
							}
						}
					}
					sb.append("(");
					sb.append(StringUtils.join(tmpList, " or "));
					sb.append(")");
				} else {
					String conSql = buildHql(filter.getPropertyName(), filter.getMatchValue(), filter
							.getMatchType(), filter.getValues(), paramList);
					sb.append(conSql);
				}
			} else {//包含多个属性需要比较的情况,进行or处理.
				sb.append(" and ");
				StringBuffer orSb = new StringBuffer();
				//orSb.append(" 1=1 ");
				int i = 0;
				for (String param : filter.getPropertyNames()) {
					if(i > 0) {
						orSb.append(" or ");
					}
					String conSql = buildHql(param, filter.getMatchValue(), filter
							.getMatchType(), filter.getValues(), paramList);
					orSb.append(conSql);
					i++;
				}
				sb.append("(");
				sb.append(orSb.toString());
				sb.append(")");
			}
		}
		return sb.toString();
	}
	
	/**
	 * 根据PropertyFilter动态生成hql语句
	 * 
	 * @param hqlSelect
	 * @param filters
	 * @param paramMap
	 * @return
	 */
	protected String buildHqlByPropertyFilter(final String hqlSelect, final List<PropertyFilter> filters, final Map<String, Object> paramMap) {
		StringBuffer sb = new StringBuffer();
		sb.append(hqlSelect);
		int beginPos = hqlSelect.toLowerCase().indexOf("where");
		if(beginPos == -1) {
			sb.append(" where 1=1");
		}
		for (PropertyFilter filter : filters) {
			if (!filter.hasMultiProperties()) { //只有一个属性需要比较的情况.
				sb.append(" and ");
				String param = filter.getPropertyName();
				String conSql = buildHql(param, filter.getMatchValue(), filter
						.getMatchType());
				sb.append(conSql);
				switch(filter.getMatchType()) {
					case IN:
						paramMap.put(param, filter.getValues());
						break;
					case LIKE:
						String[] pvs = StringUtils.split(filter.getMatchValue().toString());
						int j = 1;
						for(String pv : pvs) {
							paramMap.put(param + j++, "%" + pv + "%");
						}
						//paramMap.put(param, "%"+filter.getMatchValue().toString().trim()+"%");
						break;	
					case LIKEE:
						paramMap.put(param, filter.getMatchValue().toString().trim());
						break;	
					case LIKEL:
						paramMap.put(param, filter.getMatchValue().toString().trim()+"%");
						break;	
					case LIKER:
						paramMap.put(param, "%"+filter.getMatchValue().toString().trim());
						break;	
					case LE:
						paramMap.put(param+"1", filter.getMatchValue());
						break;
					case LT:
						paramMap.put(param+"1", filter.getMatchValue());
						break;
					default:
						paramMap.put(param, filter.getMatchValue());
				}
			} else {//包含多个属性需要比较的情况,进行or处理.
				sb.append(" and ");
				StringBuffer orSb = new StringBuffer();
				//orSb.append(" 1=1 ");
				int i = 0;
				for (String param : filter.getPropertyNames()) {
					if(i > 0) {
						orSb.append(" or ");
					}
					String conSql = buildHql(param, filter.getMatchValue(), filter
							.getMatchType());
					orSb.append(conSql);
					switch(filter.getMatchType()) {
						case IN:
							paramMap.put(param, filter.getValues());
							break;
						case LIKE:
							String[] pvs = StringUtils.split(filter.getMatchValue().toString());
							int j = 1;
							for(String pv : pvs) {
								paramMap.put(param + j++, "%" + pv + "%");
							}
							//paramMap.put(param, "%"+filter.getMatchValue().toString().trim()+"%");
							break;
						case LIKEE:
							paramMap.put(param, filter.getMatchValue().toString().trim());
							break;	
						case LIKEL:
							paramMap.put(param, filter.getMatchValue().toString().trim()+"%");
							break;	
						case LIKER:
							paramMap.put(param, "%"+filter.getMatchValue().toString().trim());
							break;	
						case LE:
							paramMap.put(param+"1", filter.getMatchValue());
							break;
						case LT:
							paramMap.put(param+"1", filter.getMatchValue());
							break;	
						default:
							paramMap.put(param, filter.getMatchValue());
					}
					i++;
				}
				sb.append("(");
				sb.append(orSb.toString());
				sb.append(")");
			}
		}
		return sb.toString();
	}
	
	/**
	 * 动态生成查询条件及参数
	 * 
	 * @param propertyName
	 * @param propertyValue
	 * @param matchType
	 * @param values
	 * @param paramList
	 * @return
	 */
	protected String buildHql(final String propertyName, final Object propertyValue, 
			final MatchType matchType, final Collection values, final List<Object> paramList) {
		Assert.hasText(propertyName, "propertyName不能为空");
		StringBuffer sb = new StringBuffer();
		//根据MatchType构造criterion
		sb.append("a.");
		sb.append(propertyName);
		switch (matchType) {
		case EQ:
			sb.append(" = ?");
			paramList.add(propertyValue);
			break;
		case NE:
			sb.append(" <> ?");
			paramList.add(propertyValue);
			break;
		case LIKE:
			sb.append(" like ?");
			paramList.add("%" + propertyValue + "%");
			break;
		case LIKEE:
			sb.append(" like ?");
			paramList.add(propertyValue);
			break;	
		case LIKEL:
			sb.append(" like ?");
			paramList.add(propertyValue + "%");
			break;	
		case LIKER:
			sb.append(" like ?");
			paramList.add("%" + propertyValue);
			break;		
		case LE:
			sb.append(" <= ?");
			paramList.add(propertyValue);
			break;
		case LT:
			sb.append(" < ?");
			paramList.add(propertyValue);
			break;
		case GE:
			sb.append(" >= ?");
			paramList.add(propertyValue);
			break;
		case GT:
			sb.append(" > ?");
			paramList.add(propertyValue);
			break;
		case IN:
			sb.append(" in (");
			StringBuffer sbIn = new StringBuffer();
			for(Object c : values) {
				sbIn.append(",?");
				paramList.add(c);
			}
			sb.append(sbIn.substring(1));
			sb.append(")");
			break;
		case USER:
			sb.append(" in (select ur.ID from SYS_USER ur where (ur.LOGIN_NAME like ?) or (ur.REAL_NAME like ?))");
			String pv = "%" + propertyValue + "%";
			paramList.add(pv);
			paramList.add(pv);
			break;
		default:
			sb.append(" = ?");
			paramList.add(propertyValue);
			break;
		}
		return sb.toString();
	}
	
	/**
	 * 根据PropertyFilter动态生成sql语句
	 * 
	 * @param hqlSelect
	 * @param filters
	 * @return
	 */
	public String buildHqlByPropertyFilter(final String hqlSelect, final List<PropertyFilter> filters) {
		StringBuffer sb = new StringBuffer();
		sb.append(hqlSelect);
		int beginPos = hqlSelect.toLowerCase().indexOf("where");
		if(beginPos == -1) {
			sb.append(" where 1=1");
		}
		List<PropertyFilter> likeFilters = Lists.newArrayList();
		for (PropertyFilter filter : filters) {
			if (!filter.hasMultiProperties()) { //只有一个属性需要比较的情况.
				sb.append(" and ");
				String conSql = buildHql(filter.getPropertyName(), filter.getMatchValue(), filter
						.getMatchType());
				sb.append(conSql);
				if(filter.getMatchType() == MatchType.LIKE) {//特殊处理
					String[] pvs = StringUtils.split(filter.getMatchValue().toString());
					int j = 1;
					for(String pv : pvs) {
						likeFilters.add(new PropertyFilter("LIKES_" + filter.getPropertyName() + j++, pv));
					}
				}
			} else {//包含多个属性需要比较的情况,进行or处理.
				sb.append(" and ");
				StringBuffer orSb = new StringBuffer();
				//orSb.append(" 1=1 ");
				int i = 0;
				for (String param : filter.getPropertyNames()) {
					if(i > 0) {
						orSb.append(" or ");
					}
					String conSql = buildHql(param, filter.getMatchValue(), filter
							.getMatchType(), filter.getPropertyNames()[0]); //默认第1个参数作为冒号参数名
					orSb.append(conSql);
					i++;
				}
				sb.append("(");
				sb.append(orSb.toString());
				sb.append(")");
			}
		}
		filters.addAll(likeFilters);
		return sb.toString();
	}
	
	/**
	 * 动态生成查询条件
	 * 
	 * @param propertyName
	 * @param propertyValue
	 * @param matchType
	 * @param values
	 * @return
	 */
	public String buildHql(final String propertyName, final Object propertyValue, 
			final MatchType matchType) {
		Assert.hasText(propertyName, "propertyName不能为空");
		StringBuffer sb = new StringBuffer();
		//根据MatchType构造criterion
		if(!MatchType.LIKE.equals(matchType)) {
			sb.append(propertyName);
		}
		switch (matchType) {
		case EQ:
			sb.append(" = :");
			sb.append(propertyName);
			break;
		case NE:
			sb.append(" <> :");
			sb.append(propertyName);
			break;
		case LIKE:
			String[] pvs = StringUtils.split(propertyValue.toString());
			int i = 1;
			sb.append("(");
			for(String pv : pvs) {
				if(i > 1) {
					sb.append(" and ");
				}
				sb.append(propertyName);
				sb.append(" like :");
				sb.append(propertyName + i++);
			}
			sb.append(")");
			break;
		case LIKEE:
		case LIKEL:
		case LIKER:
			sb.append(" like :");
			sb.append(propertyName);
			break;	
		case LE:
			sb.append(" <= :");
			sb.append(propertyName+"1");
			break;
		case LT:
			sb.append(" < :");
			sb.append(propertyName+"1");
			break;
		case GE:
			sb.append(" >= :");
			sb.append(propertyName);
			break;
		case GT:
			sb.append(" > :");
			sb.append(propertyName);
			break;
		case IN:
			sb.append(" in (:");
			sb.append(propertyName);
			sb.append(")");
			break;
		default:
			sb.append(" = :");
			sb.append(propertyName);
			break;
		}
		return sb.toString();
	}
	
	/**
	 * 动态生成查询条件，解决OR条件动态拼装bug
	 * 
	 * @param propertyName
	 * @param propertyValue
	 * @param matchType
	 * @param propertyNameParam -- 冒号后参数名
	 * @return
	 */
	public String buildHql(final String propertyName, final Object propertyValue, 
			final MatchType matchType, final String propertyNameParam) {
		Assert.hasText(propertyName, "propertyName不能为空");
		StringBuffer sb = new StringBuffer();
		//根据MatchType构造criterion
		sb.append(propertyName);
		switch (matchType) {
		case EQ:
			sb.append(" = :");
			sb.append(propertyNameParam);
			break;
		case NE:
			sb.append(" <> :");
			sb.append(propertyNameParam);
			break;
		case LIKE:
		case LIKEE:
		case LIKEL:
		case LIKER:	
			sb.append(" like :");
			sb.append(propertyNameParam);
			break;
		case LE:
			sb.append(" <= :");
			sb.append(propertyNameParam);
			break;
		case LT:
			sb.append(" < :");
			sb.append(propertyNameParam);
			break;
		case GE:
			sb.append(" >= :");
			sb.append(propertyNameParam);
			break;
		case GT:
			sb.append(" > :");
			sb.append(propertyNameParam);
			break;
		case IN:
			sb.append(" in (:");
			sb.append(propertyNameParam);
			sb.append(")");
			break;
		default:
			sb.append(" = :");
			sb.append(propertyNameParam);
			break;
		}
		return sb.toString();
	}
	
	/**
	 * 动态生成orderby语句
	 * 
	 * @param hql
	 * @param page
	 * @return
	 */
	protected String buildOrderByHql(String hql, final Page<T> page) {
		StringBuffer sb = new StringBuffer();
		sb.append(hql);
		if (page.isOrderBySetted()) {
			String[] orderByArray = StringUtils.split(page.getOrderBy(), ',');
			String[] orderArray = StringUtils.split(page.getOrder(), ',');
			sb.append(" order by");
			Assert.isTrue(orderByArray.length == orderArray.length, "分页多重排序参数中,排序字段与排序方向的个数不相等");
			for (int i = 0, len=orderByArray.length; i < len; i++) {
				sb.append(" a.");
				sb.append(orderByArray[i]);
				if (Page.ASC.equals(orderArray[i])) {	
					sb.append(" asc");
				} else {
					sb.append(" desc");
				}
				if(i < len-1) {
					sb.append(",");
				}
			}
		}
		return sb.toString();
	}
	
	/**
	 * HQL实现方式，根据排序及查询条件，查找指定ID记录的上一条、下一条、第一条、最后一条记录
	 * 目前只支持1个排序条件
	 * @param hqlSelect hql语句
	 * @param page 排序条件
	 * @param filters 查询条件
	 * @param pkName 主键名称
	 * @param id 主键
	 * @param findType 记录类型，详见FIND_TYPE_FIRST等
	 * @return
	 */
	public T findBy(String hqlSelect, final Page<T> page, final List<PropertyFilter> filters, String pkName, final PK id, String findType) {
		List<Object> paramList = new ArrayList<Object>();
		StringBuffer hql = new StringBuffer();
		hql.append(buildHqlByPropertyFilter(hqlSelect, filters, paramList));
		String[] orderByArray = StringUtils.split(page.getOrderBy(), ',');
		String[] orderArray = StringUtils.split(page.getOrder(), ',');
		String orderBy = "id";
		if(null!=orderByArray) {
			orderBy = orderByArray[0];
		}
		String order = "desc";
		if(null!=orderArray) {
			order = orderArray[0];
		}
		StringBuffer whereSb = new StringBuffer();
		StringBuffer orderBySb = new StringBuffer();
		if(FIND_TYPE_FIRST.equals(findType)) { //第一条
			if (Page.ASC.equals(order)) {
				orderBySb.append(" order by a.");
				orderBySb.append(orderBy);
				orderBySb.append(" asc");
			} else {
				orderBySb.append(" order by a.");
				orderBySb.append(orderBy);
				orderBySb.append(" desc");
			}
		} else if(FIND_TYPE_LAST.equals(findType)) { //最后一条
			if (Page.ASC.equals(order)) {
				orderBySb.append(" order by a.");
				orderBySb.append(orderBy);
				orderBySb.append(" desc");
			} else {
				orderBySb.append(" order by a.");
				orderBySb.append(orderBy);
				orderBySb.append(" asc");
			}
		} else if(FIND_TYPE_PRE.equals(findType)) { //上一条
			StringBuffer orderByFieldSb = new StringBuffer();
			orderByFieldSb.append("select ");
			orderByFieldSb.append(EncodeUtils.propertyToField(orderBy));
			orderByFieldSb.append(" from ");
			orderByFieldSb.append(EncodeUtils.entityNameToTableName(entityName));
			orderByFieldSb.append(" where ");
			orderByFieldSb.append(pkName);
			orderByFieldSb.append(" = ?");
			paramList.add(id);
			//不等于当前值
			whereSb.append(" and a.");
			whereSb.append(pkName);
			whereSb.append(" <> ?");
			paramList.add(id);
			if (Page.ASC.equals(order)) {
				whereSb.append(" and a.");
				whereSb.append(orderBy);
				whereSb.append(" <= (");
				whereSb.append(orderByFieldSb.toString());
				whereSb.append(")");
				orderBySb.append(" order by a.");
				orderBySb.append(orderBy);
				orderBySb.append(" desc");
			} else {
				whereSb.append(" and a.");
				whereSb.append(orderBy);
				whereSb.append(" >= (");
				whereSb.append(orderByFieldSb.toString());
				whereSb.append(")");
				orderBySb.append(" order by a.");
				orderBySb.append(orderBy);
				orderBySb.append(" asc");
			}
		} else { //默认下一条
			StringBuffer orderByFieldSb = new StringBuffer();
			orderByFieldSb.append("select ");
			orderByFieldSb.append(EncodeUtils.propertyToField(orderBy));
			orderByFieldSb.append(" from ");
			orderByFieldSb.append(EncodeUtils.entityNameToTableName(entityName));
			orderByFieldSb.append(" where ");
			orderByFieldSb.append(pkName);
			orderByFieldSb.append(" = ?");
			paramList.add(id);
			//不等于当前值
			whereSb.append(" and a.");
			whereSb.append(pkName);
			whereSb.append(" <> ?");
			paramList.add(id);
			if (Page.ASC.equals(order)) {
				whereSb.append(" and a.");
				whereSb.append(orderBy);
				whereSb.append(" >= (");
				whereSb.append(orderByFieldSb.toString());
				whereSb.append(")");
				orderBySb.append(" order by a.");
				orderBySb.append(orderBy);
				orderBySb.append(" asc");
			} else {
				whereSb.append(" and a.");
				whereSb.append(orderBy);
				whereSb.append(" <= (");
				whereSb.append(orderByFieldSb.toString());
				whereSb.append(")");
				orderBySb.append(" order by a.");
				orderBySb.append(orderBy);
				orderBySb.append(" desc");
			}
		}
		hql.append(whereSb.toString());
		hql.append(orderBySb.toString());
		Query query = createSQLQuery(hql.toString(), paramList.toArray());
		query.setFirstResult(0);
		query.setMaxResults(1);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return (T)query.uniqueResult();
	}
	
	/**
	 * 根据查询SQL与参数列表创建Query对象.
	 * 与find()函数可进行更加灵活的操作.
	 * 
	 * @param values 数量可变的参数,按顺序绑定.
	 */
	public Query createSQLQuery(final String queryString, final Object... values) {
		Assert.hasText(queryString, "queryString不能为空");
		Query query = getSession().createSQLQuery(queryString);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				query.setParameter(i, values[i]);
			}
		}
		return query;
	}

	/**
	 * 根据查询SQL与参数列表创建Query对象.
	 * 与find()函数可进行更加灵活的操作.
	 * 
	 * @param values 命名参数,按名称绑定.
	 */
	public Query createSQLQuery(final String queryString, final Map<String, ?> values) {
		Assert.hasText(queryString, "queryString不能为空");
		Query query = getSession().createSQLQuery(queryString);
		if (values != null) {
			query.setProperties(values);
		}
		return query;
	}
	
	/**
	 * 清除缓存对象
	 * 
	 * @param object
	 */
	public void evict(Object object) {
		sessionFactory.getCurrentSession().evict(object);
	}
	
	/**
	 * 根据查询SQL与参数列表创建Query对象.
	 * 与find()函数可进行更加灵活的操作.
	 * 
	 * @param queryString
	 * @param values   命名参数,按名称绑定.
	 * @return
	 */
	public org.hibernate.query.Query createNativeQuery(final String  queryString,final Object... values){
		Assert.hasText(queryString,"queryString不能为空");
		org.hibernate.query.Query query = getSession().createNativeQuery(queryString);
		if(values !=null){
			for(int i=0;i < values.length; i++) {
				query.setParameter(i+1, values[i]);
			}			
		}
		return query;
	}
	
	/**
	 * 通过原生SQL来查找
	 * @param sql
	 * @param value
	 */
	public List<String> findSingleValueBySQL(String queryString,Object... values){
		org.hibernate.query.Query query = createNativeQuery(queryString, values);
		List<String> resultList = query.getResultList();
		return resultList;
		
	}
	
	/**
	 * 通过原生SQL来查找
	 * @param sql
	 * @param value
	 */
	public List<Object[]> findBySQL(String queryString,Object... values){
		org.hibernate.query.Query query = createNativeQuery(queryString, values);
		List<Object[]> resultList = query.getResultList();
		return resultList;
		
	}
	
}
